
package hk.ces.oxfam.patch.transaction;

import hk.ces.oxfam.patch.TransactionPatch;
import hk.ces.oxfam.patch.TransactionStrategy;
import hk.ces.oxfam.patch.TransactionStrategyType;
import hk.ces.oxfam.patch.result.SQLResult;
import hk.ces.oxfam.patch.sql.ExcuteUtil;
import hk.ces.oxfam.patch.sql.PreparedSQL;
import hk.ces.oxfam.patch.sql.QueryUtil;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;

/**
 *
 * @author ReutyCheng
 */
@TransactionStrategy(TransactionStrategyType.ROLLBACK)
public class For20110427ProductionPatch implements TransactionPatch
{
  private static final int PREVIOUS_DEFAULT_EVENT_ID_IN_PRODUCTION = 1;
  
  private final Map<Integer, Integer> eventYearMapIdToYear = new HashMap<Integer, Integer>();

  public void run(Connection connection) throws SQLException
  {
    // Prepare
    this.prepareEvents(connection);

    // Patch
    this.updateEventChineseName(connection);
    this.updateSlotEvent(connection);
    this.updateTypeAllocationEvent(connection);

    // Test
//    this.clearResult(connection);
  }

  private void prepareEvents(Connection connection) throws SQLException
  {
    String getAllEventYearSQL = "SELECT ID, EVENTYEAR FROM OTWEVENT";
    SQLResult result = QueryUtil.query(connection, new PreparedSQL(getAllEventYearSQL, new Object[]{}));
    List<Object[]> resultList = result.getResultsList();
    for (Object[] objects : resultList)
    {
      eventYearMapIdToYear.put((Integer) objects[0], (Integer) objects[1]);
    }
  }

  private void updateEventChineseName(Connection connection) throws SQLException
  {
    int resultCount = 0;
    for (Map.Entry<Integer, Integer> entry : eventYearMapIdToYear.entrySet())
    {
      Logger.getLogger(this.getClass()).debug("Update Event[id=" + entry.getKey() + "] with Chinese Name[" + "\u6a02\u65bd\u6bc5\u884c\u8005" + entry.getValue() + "]");
      String updateAllEventChineseNameSQL = "UPDATE OTWEVENT SET NAMECH = ? WHERE ID = ? AND (NAMECH IS NULL OR NAME = ?)";
      resultCount += ExcuteUtil.excute(connection, new PreparedSQL(updateAllEventChineseNameSQL, new Object[]{"\u6a02\u65bd\u6bc5\u884c\u8005" + entry.getValue(), entry.getKey(), ""}));
    }
    Logger.getLogger(this.getClass()).debug("Total updated " + resultCount + " record(s)");
  }

  private void updateSlotEvent(Connection connection) throws SQLException
  {
    Logger.getLogger(this.getClass()).debug("Udpate Event in OTWTeamMemberSlot with eventId[" + PREVIOUS_DEFAULT_EVENT_ID_IN_PRODUCTION + "]");
    String udpateSlotEventSQL = "UPDATE OTWTEAMMEMBERSLOT SET EVENT_ID = ? WHERE EVENT_ID IS NULL";
    int resultCount = ExcuteUtil.excute(connection, new PreparedSQL(udpateSlotEventSQL, new Object[]{PREVIOUS_DEFAULT_EVENT_ID_IN_PRODUCTION}));
    Logger.getLogger(this.getClass()).debug("Total updated " + resultCount + " record(s)");
  }

  private void updateTypeAllocationEvent(Connection connection) throws SQLException
  {
    String getAllTypeAndAllocation = "SELECT ID, EVENT_ID, TEAMTYPE_ID, TEAMALLOCATION_ID FROM TEAMNOSETTING";
    SQLResult result = QueryUtil.query(connection, new PreparedSQL(getAllTypeAndAllocation, new Object[]{}));
    List<Object[]> settingEventTypeAllocationIds = result.getResultsList();

    int resultCount = 0;
    for (Object[] settingEventTypeAllocationId : settingEventTypeAllocationIds)
    {
      int column = 0;

      int settingId = (Integer) settingEventTypeAllocationId[column++];
      int eventId = (Integer) settingEventTypeAllocationId[column++];
      int teamTypeId = (Integer) settingEventTypeAllocationId[column++];
      int allocationId = (Integer) settingEventTypeAllocationId[column++];

      Logger.getLogger(this.getClass()).debug("Udpate TeamType[id=" + teamTypeId + "], TeamAllocation[id=" + allocationId + "] in TeamNoSetting[id=" + settingId + "] with eventId[" + eventId + "]");
      
      String updateTeamTypeEventSQL = "UPDATE OTWTEAMTYPE SET EVENT_ID = ? WHERE EVENT_ID IS NULL AND ID = ?";
      String updateAllocationEventSQL = "UPDATE OTWTEAMALLOCATION SET EVENT_ID = ? WHERE EVENT_ID IS NULL AND ID = ?";
      int currentResultCount = 0;
      currentResultCount += ExcuteUtil.excute(connection, new PreparedSQL(updateTeamTypeEventSQL, new Object[]{eventId, teamTypeId}));
      currentResultCount += ExcuteUtil.excute(connection, new PreparedSQL(updateAllocationEventSQL, new Object[]{eventId, allocationId}));
      Logger.getLogger(this.getClass()).debug("-- Current updated " + currentResultCount + " record(s)");

      resultCount += currentResultCount;
    }
    Logger.getLogger(this.getClass()).debug("Total updated " + resultCount + " record(s) from TeamNoSetting");

    Logger.getLogger(this.getClass()).debug("Updated other TeamType / Allocation by previous default event id[" + PREVIOUS_DEFAULT_EVENT_ID_IN_PRODUCTION + "]");
    String updateTeamTypeEventSQL = "UPDATE OTWTEAMTYPE SET EVENT_ID = ? WHERE EVENT_ID IS NULL";
    String updateAllocationEventSQL = "UPDATE OTWTEAMALLOCATION SET EVENT_ID = ? WHERE EVENT_ID IS NULL";
    int currentResultCount = 0;
    currentResultCount += ExcuteUtil.excute(connection, new PreparedSQL(updateTeamTypeEventSQL, new Object[]{PREVIOUS_DEFAULT_EVENT_ID_IN_PRODUCTION}));
    currentResultCount += ExcuteUtil.excute(connection, new PreparedSQL(updateAllocationEventSQL, new Object[]{PREVIOUS_DEFAULT_EVENT_ID_IN_PRODUCTION}));
    Logger.getLogger(this.getClass()).debug("Total updated " + currentResultCount + " record(s) for other records");
  }

  private void clearResult(Connection connection) throws SQLException
  {
    String clearEventChineseNameSQL = "UPDATE OTWEVENT SET NAMECH = ?";
    ExcuteUtil.excute(connection, new PreparedSQL(clearEventChineseNameSQL, new Object[]{null}));
    String clearSlotEventSQL = "UPDATE OTWTEAMMEMBERSLOT SET EVENT_ID = ?";
    ExcuteUtil.excute(connection, new PreparedSQL(clearSlotEventSQL, new Object[]{null}));
    String clearTeamTypeEventSQL = "UPDATE OTWTEAMTYPE SET EVENT_ID = ?";
    ExcuteUtil.excute(connection, new PreparedSQL(clearTeamTypeEventSQL, new Object[]{null}));
    String clearAllocationEventSQL = "UPDATE OTWTEAMALLOCATION SET EVENT_ID = ?";
    ExcuteUtil.excute(connection, new PreparedSQL(clearAllocationEventSQL, new Object[]{null}));
  }
}
